﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace NORM.DataBase
{
    #region MySqlHelper
    /// <summary>
    /// 
    /// </summary>
    internal class MySqlHelper
    {
        public MySqlTransaction tran = null;

        public int Execute(string connectionString, List<string> cmdTextList)
        {
            return 0;
        }

        public int Execute(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int Execute(string connectionString, CommandType cmdType, string cmdText, int timeout, params DbParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    cmd.CommandTimeout = timeout;
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int Execute(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteNonQuery();
            }
        }

        public int Execute(DbConnection conn, CommandType cmdType, string cmdText, int timeout, params DbParameter[] cmdParms)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                cmd.CommandTimeout = timeout;
                return cmd.ExecuteNonQuery();
            }
        }

        public object GetScalar(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public object GetScalar(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteScalar();
            }
        }

        public DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (MySqlDataAdapter da = new MySqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds;
                    }
                }
            }
        }

        public DataSet GetDataSet(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                DataSet ds = new DataSet();
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                try
                {
                    using (MySqlDataAdapter da = new MySqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        da.Fill(ds);
                        return ds;
                    }
                }
                catch (Exception ex)
                {
                    ds = new DataSet();
                    MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable("Table");
                    DataTable schemaTable = reader.GetSchemaTable();
                    foreach (DataRow dataRow in (InternalDataCollectionBase)schemaTable.Rows)
                    {
                        string typeName = string.Concat(dataRow["DataType"]);
                        string columnName = string.Concat(dataRow["ColumnName"]);
                        switch (typeName)
                        {
                            case "SqlTypes.BitString":
                                typeName = "System.Boolean";
                                break;
                        }
                        table.Columns.Add(new DataColumn(columnName, Type.GetType(typeName)));
                    }

                    while (reader.Read())
                    {
                        DataRow row = table.NewRow();
                        foreach (DataColumn dc in table.Columns)//InternalDataCollectionBase
                        {
                            object Value = reader[dc.ColumnName];
                            if (Value != null && !DBNull.Value.Equals(Value))
                                row[dc.ColumnName] = Value;
                        }
                        table.Rows.Add(row);
                    }

                    reader.Dispose();
                    ds.Tables.Add(table);
                    ds.Tables.Add(schemaTable);

                    //未开启事务，则查询完成关闭连接
                    //if (!TransactionBegin)
                    //    conn.Close();

                }
                return ds;
            }
        }

        public DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return GetDataSet(connectionString, cmdType, cmdText, cmdParms).Tables[0];
        }

        public DataTable GetDataTable(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return GetDataSet(conn, cmdType, cmdText, cmdParms).Tables[0];
        }

        public DbDataReader GetReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
        }

        public DbDataReader GetReader(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        public DataTable GetDbSchema(string connectionString)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            NORM.Common.ConnectionString Connect = new Common.ConnectionString(connectionString);

            StringBuilder strSql = new StringBuilder();
            //strSql.Append(" select [type] as [table_type] ,[tbl_name] as [table_name] from Sql_master ");
            strSql.Append(" select table_name ,table_type from information_schema.tables ");
            strSql.Append(" where TABLE_SCHEMA='" + Connect.DataBaseName + "' ");
            strSql.Append(" union ");
            strSql.Append(" select name as  table_name,type as table_type from mysql.proc ");
            strSql.Append(" where db = '" + Connect.DataBaseName + "' ");
            strSql.Append(" ORDER BY table_type ASC ,table_name ASC ");

            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "base table": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "procedure": ttype = "procedure"; break;
                    case "function": ttype = "function"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
        }

        public DataTable GetDbSchema(string connectionString, string collectionName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            NORM.Common.ConnectionString Connect = new Common.ConnectionString(connectionString);

            if (!string.IsNullOrEmpty(collectionName))
            {
                collectionName = collectionName.ToLower();
                switch (collectionName)
                {
                    case "u": collectionName = "base table"; break;
                    case "v": collectionName = "view"; break;
                }
            }            

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * ");
            strSql.Append(" from ( select table_name ,table_type from information_schema.tables ");
            strSql.Append(" where TABLE_SCHEMA='" + Connect.DataBaseName + "' ");
            strSql.Append(" union ");
            strSql.Append(" select name as  table_name,type as table_type from mysql.proc ");
            strSql.Append(" where db = '" + Connect.DataBaseName + "' ");
            strSql.Append(" ) t where table_type='" + collectionName + "' ");
            strSql.Append(" ORDER BY table_type ASC ,table_name ASC ");

            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "base table": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "procedure": ttype = "procedure"; break;
                    case "function": ttype = "function"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
        }

        public bool AddColumDescribtion(string connectionString, string tableName, string columnName, string Descrition)
        {
            return false;
        }

        public DataTable GetDependencies(string connectionString, string name, int type)
        {
            return null;
        }

        public DataTable GetTables(string connectionString)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            NORM.Common.ConnectionString Connect = new Common.ConnectionString(connectionString);

            StringBuilder strSql = new StringBuilder();         
            strSql.Append(" select table_name ,table_type from information_schema.tables ");
            strSql.Append(" where TABLE_SCHEMA='" + Connect.DataBaseName + "' and  table_type='base table' ");           
            strSql.Append(" ORDER BY table_type ASC ,table_name ASC ");

            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "base table": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "procedure": ttype = "procedure"; break;
                    case "function": ttype = "function"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
            
        }

        public DataTable GetTableColumns(string connectionString, string tableName)
        {
            DataTable dt = new DataTable(tableName);
            dt.Columns.Add(new DataColumn("colorder", typeof(Int32)));
            dt.Columns.Add(new DataColumn("ColumnName", typeof(string)));
            dt.Columns.Add(new DataColumn("TypeName", typeof(string)));
            dt.Columns.Add(new DataColumn("Length", typeof(string)));
            dt.Columns.Add(new DataColumn("CisNull", typeof(string)));
            dt.Columns.Add(new DataColumn("Describ", typeof(string)));

            NORM.Common.ConnectionString Connect = new Common.ConnectionString(connectionString);

            string strSql = "select COLUMN_NAME as Name, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH as len,";
            strSql += "COLUMN_KEY as pk, IS_NULLABLE as notnull ,COLUMN_TYPE as type ";
            strSql += "from information_schema.COLUMNS ";
            strSql += "where table_name = '" + tableName + "' and table_schema = '" + Connect.DataBaseName + "'; ";        
            DataTable dt_columns = GetDataTable(connectionString, CommandType.Text, strSql, null);
            int index = 1;
            foreach (DataRow dr in dt_columns.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["colorder"] = index;
                newrow["ColumnName"] = dr["name"];
                string len = dr["type"] + "";
                string typename = dr["type"] + "";
                if (typename.Contains("(") && typename.Contains(")"))
                {
                    len = len.Substring(len.LastIndexOf("(") + 1);
                    len = len.Substring(0, len.LastIndexOf(")"));
                    typename = typename.Substring(0, typename.LastIndexOf("("));
                }
                else
                {
                    len = "";
                }

                if (typename.ToLower().Equals("varchar") && string.IsNullOrEmpty(len))
                {
                    len = "50";
                }

                newrow["TypeName"] = typename;//nvarchar(50)
                newrow["Length"] = len;

                string pk = "";
                pk = dr["pk"] + "";
                if (pk == "PRI")
                {
                    pk = "pk,";
                }
                else
                {
                    pk = "";
                }
                string isnull = dr["notnull"] + "";
                if (isnull == "NO")
                {
                    isnull = "not null";
                }
                else
                {
                    isnull = "null";
                }

                newrow["CisNull"] = pk + isnull + ",";
                newrow["Describ"] = "";
                dt.Rows.Add(newrow);
                index++;
            }

            return dt;
             
        }

        public DataTable GetTabPKeyColumns(string connectionString, string TableName)
        {
            NORM.Common.ConnectionString connection = new Common.ConnectionString(connectionString);
            NORM.Common.StringPlus sql = new Common.StringPlus();
            sql.AppendLine("SELECT t.TABLE_NAME, c.COLUMN_NAME, ts.AUTO_INCREMENT ");
            sql.AppendLine("FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, ");
            sql.AppendLine("information_schema.TABLES AS ts, ");
            sql.AppendLine("information_schema.KEY_COLUMN_USAGE AS c ");
            sql.AppendLine("WHERE t.TABLE_NAME = ts.TABLE_NAME AND ts.TABLE_NAME = c.TABLE_NAME ");
            sql.AppendLine("AND t.TABLE_SCHEMA = '"+connection.DataBaseName+"' ");
            sql.AppendLine("AND t.TABLE_NAME='" + TableName + "' ");
            sql.AppendLine("AND t.CONSTRAINT_TYPE = 'PRIMARY KEY' ");
            sql.AppendLine("ORDER BY ts.AUTO_INCREMENT DESC; ");
            return GetDataTable(connectionString, CommandType.Text, sql.Value, null);
        }

        public DataTable GetTabIdentityColumns(string connectionString, string TableName)
        {
            return null;
        }

        public bool RenameTable(string connectionString, string OldName, string NewName)
        {
            return false;
        }

        public bool UpdateDataSet(string connectionString, DataSet dataSet, string srcTable)
        {
            bool rvl = false;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                using (MySqlDataAdapter da = new MySqlDataAdapter(srcTable, conn))
                {
                    MySqlCommandBuilder scb = new MySqlCommandBuilder(da);
                    da.InsertCommand = scb.GetInsertCommand();
                    da.UpdateCommand = scb.GetUpdateCommand();
                    da.DeleteCommand = scb.GetDeleteCommand();
                    da.Update(dataSet);
                    rvl = true;
                }
            }
            return rvl;
        }

        public bool UpdateDataSet(DbConnection conn, DataSet dataSet, string srcTable)
        {
            bool rvl = false;
            using (MySqlCommand cmd = new MySqlCommand(srcTable, (MySqlConnection)conn))
            {
                cmd.Transaction = tran;
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    MySqlCommandBuilder scb = new MySqlCommandBuilder(da);
                    da.InsertCommand = scb.GetInsertCommand();
                    da.UpdateCommand = scb.GetUpdateCommand();
                    da.DeleteCommand = scb.GetDeleteCommand();
                    da.Update(dataSet);
                    rvl = true;
                }
            }
            return rvl;
        }

        public DataSet GetListByPage(string connectionString, string strTab, string strFields, string strWhere, string orderby, int startIndex, int endIndex)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    string cmdText = " SELECT " + strFields + " FROM " + strTab + " ";
                    cmdText += "WHERE " + strWhere + " ORDER " + orderby + " ";
                    cmdText += "LIMIT " + (endIndex - startIndex) + " OFFSET " + startIndex + " ";

                    PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, null);
                    using (MySqlDataAdapter da = new MySqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds;
                    }

                }
            }            
        }

        /// <summary>
        /// 生成要执行的命令
        /// </summary>
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {

            try
            {
                // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                if (cmdParms != null && cmdParms.Length > 0)
                    cmdText = cmdText.Replace("@", "?").Replace(":", "?");

                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                if (trans != null)
                    cmd.Transaction = trans;
                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (DbParameter parm in cmdParms)
                    {
                        // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                        //parm.ParameterName = parm.ParameterName.Replace("?", "@").Replace(":", "@");
                        //if (parm.Direction != ParameterDirection.Output && parm.Direction != ParameterDirection.InputOutput && parm.Value == null)
                        //    parm.Value = DBNull.Value;
                        //cmd.Parameters.Add(parm);

                        // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                        string name = parm.ParameterName.Replace("@", "?").Replace(":", "?");
                        object value = parm.Value == null ? DBNull.Value : parm.Value;
                        DbParameter p = new MySqlParameter();
                        p.ParameterName = name;
                        p.DbType = parm.DbType;
                        p.Direction = parm.Direction;

                        if (p.Direction == ParameterDirection.Output
                            || p.Direction == ParameterDirection.InputOutput)
                        {
                            p = parm;
                        }
                        else
                        {
                            p.Value = value;
                            p.Size = parm.Size;
                        }

                        cmd.Parameters.Add(p);

                    }
                }
            }
            catch (Exception ex)
            {
                throw new NORMException(ExceptionType.DataBaseExceptoin, ex.Message);
            }
        }

    }
    #endregion
}
